options(echo = FALSE)

source("Rs/demographics.R")

library( "RSQLite" )

   # create a SQLite instance and create one connection.
   m <- dbDriver("SQLite")
   
   # initialize a new database to a tempfile and copy some data.frame
   # from the base package into it
   tfile <- "mydb"
   con <- dbConnect(m, dbname = tfile)

   dbListTables(con)


# [1] "personID"   "timestamp"  "group"      "lookGroup"  "friendship"
# [6] "stat"       "noPrimary"  "fromEB"     "fromSB"     "fromNB"    
#[11] "fromSF"     "fromPN"     "fromSE"     "referred"   "friends"   
#[16] "geekCode"   "coming"     "notes"      "oneway"     "matches"   
#[21] "first"      "last"       "isMan"      "isWoman"    "isTransM"  
#[26] "isTransW"   "isGQ"       "gender"     "age"        "isKinky"   
#[31] "lookM"      "lookW"      "lookTransM" "lookTransW" "lookGQ"    
#[36] "lookNone"   "lookGender" "lookKink"   "minAge"     "maxAge"   


   qry = "SELECT rr.psdid as personID, rr.event, seek_groups as lookGroup, groups_match_all, friend_dates, rr.event, seeking_primary as noPrimary, location, referred_by, pals, first_name, last_name, gender, age, kinky as isKinky, seek_gender, seek_age_min, seek_age_max, seek_kinkiness as lookKink
     FROM register_regrecord rr, register_regrecord_people con, register_person p
     WHERE rr.id = con.regrecord_id AND con.person_id = p.id AND  rr.event= 'macaw1'"

   rs <- dbSendQuery(con, qry)
   dat <- fetch(rs, n = -1)      # extract all remaining data

	print( names(dat) )
	
	# generate genders
	genderparse = function( G, gs=c("M","F","TM","TF","Q") ) {
		gends = strsplit( G, "," )
		t( sapply( gends, function( X ) { 
		!is.na(pmatch( gs, X )) } ) )
	}
	
	gends = genderparse( dat$gender )
	colnames(gends) = c("isMan","isWoman","isTransM","isTransW","isGQ")
	dat = cbind( dat, gends )
	
	lookgends = genderparse( dat$seek_gender )
	colnames(lookgends) = c("lookM","lookW","lookTransM","lookTransW","lookGQ")
	dat = cbind( dat, lookgends )
		
	locs = genderparse( dat$location, lss<-c("SF","NB","EB","PN","SB","SE") )
	colnames(locs) = paste("from",lss, sep="")
	dat = cbind( dat, locs )
	
	# identify groups
	dups = dat$personID[ duplicated( dat$personID ) ]
	dat$group = dat$personID %in% dups
	
   # clean up
   dbClearResult(rs)
   dbDisconnect(con)
   file.info(tfile)
   
   
   print(load.demographics(dat))
	
	
   